import pymysql

class Mysql(object):
    def __init__(self):
        try:
            self.db = pymysql.connect(host='121.37.190.129', user='tomliu', passwd='1qaz2wsx!@SYS', db='coronavirusdb', port=3306,
                         charset='utf8')
            self.cursor = self.db.cursor()

        except:
            print("连接失败！")
    #查询数据函数
    def getData(self, keywords):
        sql =" SELECT *   FROM  articleshanghai WHERE street LIKE  '%s'" % ('%%%s%%' % keywords)
        self.cursor.execute(sql)
        results = self.cursor.fetchall()
        self.cursor.close()
        # 关闭数据库连接
        self.db.close()
        return results


    # 定义一个存入数据库的函数
    def insert_tableshanghai(self, area_name, latest_diagnosis_time, street, unsealing_time):

        # 插入数据
        query_sql = 'select * from articleshanghai where area_name=%s and latest_diagnosis_time=%s and street=%s'
        sql = 'insert into articleshanghai (area_name,latest_diagnosis_time,street,unsealing_time) values (%s, %s, %s,%s)'
        # 使用 cursor() 方法创建一个游标对象 cursor
        try:
            query_value = (area_name, latest_diagnosis_time, street)
            # 执行 sql 语句
            self.cursor.execute(query_sql, query_value)
            results = self.cursor.fetchall()
            if len(results) == 0:
                value = (area_name, latest_diagnosis_time, street, unsealing_time)
                self.cursor.execute(sql, value)
                # 提交事务
                self.db.commit()

                return True
            else:

                return False
        except BaseException as e:  # 如果发生错误则回滚
            self.db.rollback()
            print(e)
        finally:  # 关闭游标连接
            self.cursor.close()
            # 关闭数据库连接
            self.db.close()